#!/usr/bin/env python
#-*-coding: utf-8 -*-

import sys
import xlrd
import bd_module_4
from unidecode import unidecode

##
# metodo que verifica o ficheiro fornecido tem a extenção correcta
# tenta abrir o ficheiro e se conseguir tenta converter o 
#conteudo para UTF-8
##
def abrir_ler_dados( nome_ficheiro, nome_bd):
    '''
    Abrir um ficheiro do excel utilizando o 
    modulo de leitura de ficheiros excel em python, xlrd
        nome_ficheiro - nome do ficheiro a abrir (caminho relativo ou absoluto )
    '''        
    livro = None
    if (not (nome_ficheiro[-4:] == '.xls')):
        print 'WRONG FILE GIVEN!'
        return
    else:
        try:
            livro = xlrd.open_workbook(nome_ficheiro, formatting_info=True)
        except :
            print "ERROR OPENING '{0}'".format(nome_ficheiro)
            return
        else:            
##            nome_bd = bd_module_4.operacoes_bd(nome_bd)
            conn = bd_module_4.sql_ligar_a_bd(nome_bd)
            cursor = conn.cursor()
            
##            sql_stmt = 'INSERT INTO Tabela_1_1 (sexo, inscritos, ano) VALUES(?,?,?)'
            dic_dados = {}
            keys = livro.sheet_names()
            k = 0
            
            print '****************************************'
##            imprimir_dados_pagina(livro.sheet_by_name('Tabela 2.4'))


            try:                    
                #retorrna uma lista de conjunto de dados agrupados por campos
##                obter_campos1(livro.sheet_by_name('Tabela 2.4'))
                
                for npag in range(2, livro.nsheets):
                    keys[npag] = keys[npag].replace(' ', '_',1)
                    keys[npag] = keys[npag].replace('.', '_',1)
                    keys[npag] = keys[npag].replace(' ', '',1)
                    
                    pag_dados = livro.sheet_by_index(npag)
                    atrib = obter_colunas(pag_dados)
                    
                    
                    atrib = utf_8_decoder1(atrib)
                    
                    
                    bd_module_4.sql_criar_tabelas(cursor, keys[npag], atrib)
                    conn.commit()
                    sql_stmt = criar_sql_querys_1(keys[npag], atrib)
                    conn.commit()
                    
                    print sql_stmt
                    
                    pag_dados = obter_campos1(pag_dados)
                    
                    utf_8_decoder1(pag_dados)
                    bd_module_4.sql_preencher_tabelas1(cursor, pag_dados, sql_stmt)
                    
##                    dic_dados[keys[npag]] = pag_dados
                    k +=1
                pass
                
                bd_module_4.sql_guardar_desligar_bd(conn, cursor)
            except xlrd.XLRDError:
                print 'Erro na formatação das Folhas!\n'
            
            
            
##            for npag in range(2, livro.nsheets):
##                keys[npag] = keys[npag].replace(' ', '_',1)
##                keys[npag] = keys[npag].replace('.', '_',1)
##                keys[npag] = keys[npag].replace(' ', '',1)
##                
##                pag_dados = livro.sheet_by_index(npag)
##                pag_dados = obter_dados_pagina(pag_dados)
##                dic_dados[keys[npag]] = pag_dados
##                k +=1
##                bd_module_4.preencher_tabelas(pag_dados, cursor, sql_stmt)
##                bd_module_4.guardar_desligar_bd(conn, cursor)
##                break
            pass
##            criar_sql_querys_1(dic_dados)
        pass
    pass
pass


##
#
def criar_sql_querys(dic_dados):
    '''
    recebe um dicionario com cheve de nome da 
    tabela e os campos como valo e formula apartir 
    de cada campo do dicionario uma expressao sql de criacao de tabelas
    '''
    print len(dic_dados)
    
    for k in dic_dados.keys():
        print '\n CHAVE: ',k
        for v in range(len(dic_dados.values())):
            print '\n\t', dic_dados.values()[v]
            break
        
##    for t in dic_dados.keys():
##        campo = 'CREATE TABLE {0} ({1} {2} {3})'
##        nome_tabela = t
##        print '\n nome: ', nome_tabela, '\n'
##        campos = dic_dados[t][0]
##        for valor in campos:
##            campo1+= strvalor
##            
##        valor = dic_dados[t]            #lista de campos ou atributos
##        print
##        bd_module_4.preencher_tabelas(pag_dados, cursor, sql_stmt)
##        bd_module_4.guardar_desligar_bd(conn, cursor)
    pass
    #preenchimento com os valores do dic_dados    
pass


##
#
def sql_criar_tabelas(nome_t, atributos):
    '''
    recebe um dicionario cuja cheve é nome da 
    tabela e os campos como valor e formula apartir 
    de cada campo do dicionario uma expressao sql de criacao de tabelas
    '''
    
    sql_cr = 'CREATE TABLE IF NOT EXISTS {0} (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, '.format(nome_t)

    for atrib in atributos:
        sql_cr += atrib[0] +' '+ atrib[1] + ','
        
    sql_cr = sql_cr[:sql_cr.rfind(',')]
    sql_cr += ')'
    print sql_cr
        
##            
##        valor = dic_dados[t]            #lista de campos ou atributos
##        print
##        bd_module_4.preencher_tabelas(pag_dados, cursor, sql_stmt)
##        bd_module_4.guardar_desligar_bd(conn, cursor)
    pass
    #preenchimento com os valores do dic_dados    
pass


##
#
def obter_campos(pagina):
    print pagina.name
##    print pagina.merged_cells
    
    lista1 =[]
    lista_m = {}
    for crange in pagina.merged_cells:
        rlo, rhi, clo, chi = crange
        for rowx in xrange(rlo, rhi):
            for colx in xrange(clo, chi):
               
                if ((pagina.cell_value(rlo, clo) == 'Total') or (pagina.cell_value(rlo, clo) == 'total')):
                    continue
                if ((pagina.cell_value(rlo, clo) == 'TOTAL') or (pagina.cell_value(rlo, clo) == 'PORTUGAL')): 
                    break
                else:
                    lista_m[(rowx, colx)] = pagina.cell_value(rlo, clo)
                pass
            pass
        pass
    pass
    
    parar = False
    
    for ln in xrange(2, pagina.nrows):
        dados = []
        for cl in xrange(pagina.ncols):
            cell_v = (ln, cl)
            
            # celulas a ignorar nas leitura
            if ((pagina.cell_value(ln, cl) == 'Total') or (pagina.cell_value(ln, cl) == 'total')):
                break                                   #nao ler as linhas com valores totais
            if ((pagina.cell_value(ln, cl) == 'TOTAL') or (pagina.cell_value(ln, cl) == 'PORTUGAL')): 
                parar = True                            #indicação que chegou ao fim da tabela dos dados
            if ( (pagina.cell_value(ln, cl) == '') and (cell_v in lista_m.keys())):
                                                        #se for celulas agrupadas
                print lista_m[cell_v]
                dados.append(lista_m[cell_v])
            elif (not pagina.cell_value(ln, cl) == ''): 
                dados.append(pagina.cell_value(ln, cl))
                print ' LINHA ', ln, pagina.cell_value(ln, cl)
            pass
        pass
        print '\n',dados
        tt = ()
        for v_i in xrange(len(dados)):

            if (isinstance(dados[v_i],float)):
                chave = dados[:v_i]
                dados = dados[v_i:]
                print '\n AQUI OS SSEPARADOS\n',chave, dados
                for val in dados:
                    tt = tuple(chave)
                    print 'TUPLO ',tt
##                    listaD.append((chave, val))                   #lista de tuplos de dados de uma linha
                break
            pass
        pass
        
        if (parar):
            
            break
        pass
        print '\n'
            # celulas a ignorar nas leitura
##            if ((pagina.cell_value(ln, cl) == 'Total') or (pagina.cell_value(ln, cl) == 'total')):
##                continue
##            if ((pagina.cell_value(ln, cl) == 'TOTAL') or (pagina.cell_value(ln, cl) == 'PORTUGAL')): 
##                break
##            elif (celula3 in lista_m.keys()):
##                print lista_m[celula3]
        
##            pagina.cell_value(ln, cl)
##            pagina.cell_value(ln,0)
##            pagina.cell_value(2, cl)
##
##        
##    coln_values = folha.col_values(0)
##    for valor in range(3, pagina.nrows):
##        # celulas a ignorar nas leitura
##        if ((pagina.row(valor).value == 'Total') or (pagina.row(valor).value == 'total')):
##            continue
##        if ((pagina.row(valor).value == 'TOTAL') or (pagina.row(valor).value == 'PORTUGAL')): 
##            break
##        elif ((pagina.row(valor).value == '') and )
##    pass
##    
##    lista1.sort()
##    print '\n', str(lista1)
        
##    lst_campos = pagina.row_values(2)
##    pagina.cell_value()    
##    lst_campos.apend()




##
#
def obter_campos1(pagina):

##    print pagina.merged_cells
    
    lista_m = {}
    for crange in pagina.merged_cells:
        rlo, rhi, clo, chi = crange
        for rowx in xrange(rlo, rhi):
            for colx in xrange(clo, chi):               
                if ((pagina.cell_value(rlo, clo) == 'Total') or (pagina.cell_value(rlo, clo) == 'total')):
                    continue
                if ((pagina.cell_value(rlo, clo) == 'TOTAL') or (pagina.cell_value(rlo, clo) == 'PORTUGAL')): 
                    break
                else:
                    lista_m[(rowx, colx)] = pagina.cell_value(rlo, clo)
                pass
            pass
            if ((pagina.cell_value(rlo, clo) == 'TOTAL') or (pagina.cell_value(rlo, clo) == 'PORTUGAL')): 
                break
        pass
    pass
    
    
    l =lista_m.keys()
    l.sort()
    lista_D = []                                                #lista definitiva

    for ln in xrange(2, pagina.nrows):
        dados1 = []                                             #lista dos campos de uma linha
        for cl in xrange(pagina.ncols):
            cell_v = (ln, cl)
            if ((pagina.cell_value(ln, cl) == 'Total') or 
                (pagina.cell_value(ln, cl) == 'total') or (pagina.cell_value(ln, cl) == 'HM')):
                break                                           #nao ler as linhas com valores totais
            if ((pagina.cell_value(ln, cl) == 'TOTAL') or (pagina.cell_value(ln, cl) == 'PORTUGAL')): 
                break                                    #indicação que chegou ao fim da tabela dos dados
            elif ( (pagina.cell_value(ln, cl) == '') and (cell_v in lista_m.keys())):
                                                                #se for celulas agrupadas
                
                dados1.append(lista_m[cell_v])
            elif (not pagina.cell_value(ln, cl) == ''):
                cvalue = pagina.cell_value(ln, cl)
                if (cvalue == '-'): cvalue = 0.0
                if (isinstance(cvalue, float)):
                    t_dados = list(dados1)                      #lista temporaria
                    
                    t_dados.append(cvalue)                      #acrescenta o valor por ano
                    t_dados.append(pagina.cell_value(2, cl))    #acrescenta o ano
                    lista_D.append(tuple(t_dados))              #guarda os dados agrupados
                else:
                    dados1.append(cvalue)
                pass
            pass
        pass
        
        if ((pagina.cell_value(ln, cl) == 'TOTAL') or (pagina.cell_value(ln, cl) == 'PORTUGAL')): 
            break                                    #indicação que chegou ao fim da tabela dos dados
        pass
    pass
    return lista_D
pass




def obter_colunas(f):                    
    print
    print
    colunas = []
    for c in f.row(2):
        cc = c.value
        if (cc[:3].isdigit()):
            colunas.append(('Valor', 'CHAR(10)'))
            colunas.append(('Ano', 'VARCHAR(50)'))
            break
        else:
            if (cc == ''): cc = 'Tipo'
            cc = cc.strip()
            cc = cc.replace(' ', '_')
            colunas.append((cc, 'VARCHAR(50)'))
        pass
    pass
    return colunas
pass


##
#
def criar_sql_querys_1(nome_t, nome_colunas):
    '''
    recebe um dicionario com cheve de nome da 
    tabela e os campos como valo e formula apartir 
    de cada campo do dicionario uma expressao sql de criacao de tabelas
    '''
    sql_v = ' VALUES('
    v = '?'
    nm_coluna = []
    for campo1 in nome_colunas:
        nm_coluna.append(campo1[0])
        sql_v += '?,'
    pass
    colunas = tuple(nm_coluna)
    sql_i = 'INSERT INTO {0} {1}'.format(nome_t, colunas)
    
    sql_v = sql_v[:sql_v.rfind(',')]
    sql_v += ')'
        
    sql_i = sql_i + (sql_v)
    return sql_i
    #preenchimento com os valores do dic_dados    
pass


##
#codificando os dados
def obter_dados_pagina( folha):
    '''codifica a informação em utf8 e retorna-os numa lista'''

    tmp_lst = []
    lst_dados = []
    coln_values = folha.col_values(0)
    for valor in range(3, len(coln_values)):
        # Linhas a ignorar nas pesquisas
        if ((coln_values[valor] == 'Total') or (coln_values[valor] == 'total')):
            continue
        if(coln_values[valor] == 'TOTAL'): 
            break
        else:
            tmp_lst.append(coln_values[valor])
        pass
    pass
    
    lin = 2                                     #os dados nas linha comecam sempre na linha 2
    for index in range(len(tmp_lst)):
        lin +=1                             
        for col in range(1,folha.ncols):
            if (not folha.cell_value(lin, col) == ''):
                lst_dados.append((tmp_lst[index], folha.cell_value(lin, col), folha.cell_value(2, col)))
            pass
        pass
    pass
    return lst_dados
pass


##def imprimir_dados_pagina( folha):
##    
##    tmp_lst = []
##    lst_dados = []
##    coln_values = folha.col_values(0)
##    '''retornar lista de dados'''
##    tmp_lst = []
##    lst_dados = []
##    coln_values = folha.col_values(0)
##    linha_values = folha.row_values(2)
##    print '\n', coln_values, '\n'
##    print linha_values, '\n'
##    
##    
##    
##    
##    for valor in range(3, len(coln_values)):
##        # celulas a ignorar nas pesquisas
##        if ((coln_values[valor] == 'Total') or (coln_values[valor] == 'total')):
##            continue
##        if ((coln_values[valor] == 'TOTAL') or (coln_values[valor] == 'PORTUGAL')): 
##            break
##        else:


##            v = valor
##            while(coln_values[valor+1] == ''):
##                print valor,' => ', coln_values[v], '\n'
##                valor +=1
##            print valor,' => ', coln_values[v], '\n'
##            tmp_lst.append(coln_values[valor])
##        pass
##    pass
##    
##    lin = 2                                     #os dados nas linha comecam sempre na linha 2
##    for index in range(len(tmp_lst)):
##        lin +=1
##        for col in range(1,folha.ncols):
##            val1 = folha.cell_value(lin, col)
####            while(folha.cell_value(lin+1, col) == ''):
##                
##            if (col != 0):
##                pass
##            if (not folha.cell_value(lin, col) == ''):
####                lst_dados.append((tmp_lst[index], folha.cell_value(lin, col), folha.cell_value(2, col)))
##                pass
##                print folha.cell_value(lin, col)
##            pass
##        pass
##    pass
##    return lst_dados
pass

##
#codificando os dados
def utf_8_decoder(lista):
    '''recebe uma lista e converte as strings da lista
        lista - conjunto de dados a converters
    '''
    for obj in lista:
        yield obj.decode("utf8")
    pass
pass

##
#codificando os dados
def utf_8_decoder1(lista):
    '''recebe uma lista e converte as strings da lista
        lista - conjunto de dados a converters
    '''
    n_lista = []
    for pos in xrange(len(lista)):
        tmp_lst = []
        for cmp in lista[pos]:
            
            if (isinstance(cmp, unicode)):
                tmp_lst.append(unidecode(cmp))
            else:
                tmp_lst.append(cmp)
            pass
        pass
        n_lista.append(tuple(tmp_lst))
    pass
    return n_lista
pass

##
#codificando os dados
def utf_8_decoder2(lista):
    '''recebe uma lista e converte as strings da lista
        lista - conjunto de dados a converters
    '''
    for pos in xrange(len(lista)):
        
        if (not isinstance(lista[pos], unicode)):
            tmp_lst = list(lista[pos])
            utf_8_decoder2(tmp_lst)
        else:
            lista[pos] = unidecode(lista[pos])
        pass
    pass
    return lista
pass

##
# metodo que recebe um endereco para a base de dados e os dados a guardar
# cria as tabelas apartir da lista de dados
# e salva para o disco
#
def criarBD_guardar_dados(endereco, dados):
    '''
    Verifica o endereço fornecido, cria a base de dados e as tabelas
    e com o modulo bd_module guarda os dados no disco
        endereco - caminho completo ou nome da base de dados a criar  
        dados - lista de dados a guardar no disco
    '''
    bd_module.criar_base_dados(endereco)
    
pass



if __name__ == "__main__":
    def usage():
        text = """
        usage:
            no ambiente python:
                lp1213-4708.py nF.xls cN_bd[.ext]
            
            nF.xls -> nome do ficheiro no formato excel
            cN_bd[.ext] -> nome ou caminho (com ou sem extensão)
                        do ficheiro para base de dados \n\n"""
        
        sys.stdout.write(text)
    pass
    
    if len(sys.argv) < 3:
        usage()
        sys.exit(0)
    if len(sys.argv) == 3:
        print
        abrir_ler_dados(sys.argv[1], sys.argv[2])
    pass